package miroshnichenko.gallery.dao.database;

import miroshnichenko.gallery.dao.ImageItemDao;
import miroshnichenko.gallery.domain.ImageItem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class ImageItemDaoJdbcTemplate implements ImageItemDao {

    private static final String UPLOAD_IMAGE = "INSERT INTO image_items (image_name, image_type, image_content)" +
            " VALUES (:fileName, :fileType, :fileContent)";
    private static final String RETRIEVE_IMAGE = "SELECT * FROM image_items WHERE id_item = :id_item";
    private static final String THUMBNAIL_LIST = "SELECT id_item FROM image_items LIMIT :amount OFFSET :from_item";

    private NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    @Override
    public void uploadImage(ImageItem item) {
        SqlParameterSource params = new BeanPropertySqlParameterSource(item);
        jdbcTemplate.update(UPLOAD_IMAGE, params);
    }

    @Override
    public ImageItem retrieveImage(int imageItemId) {
        SqlParameterSource params = new MapSqlParameterSource("id_item", imageItemId);
        return jdbcTemplate.queryForObject(RETRIEVE_IMAGE, params, imageItemRowMapper);
    }

    @Override
    public List<Integer> retrieveThumbnailsIdList(int fromItem, int amount) {
        Map<String, Integer> queryMap = new HashMap<String, Integer>();
        queryMap.put("amount", amount);
        queryMap.put("from_item", fromItem);

        SqlParameterSource params = new MapSqlParameterSource(queryMap);

        return jdbcTemplate.queryForList(THUMBNAIL_LIST, params, Integer.class);
    }

    private final RowMapper<ImageItem> imageItemRowMapper = new RowMapper<ImageItem>() {
        @Override
        public ImageItem mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            ImageItem imageItem = new ImageItem();

            imageItem.setFileName(resultSet.getString("image_name"));
            imageItem.setFileType(resultSet.getString("image_type"));
            imageItem.setFileContent(resultSet.getBytes("image_content"));

            return imageItem;
        }
    };
}
